検証: リストアの正常パターン
from SQL Serverのバックアップ・リストア仕様
概要・環境
SQL Serverのリストアのパターン
C. 元のデータベースが存在するサーバーに、新しいデータベース名で以前のディスクバックアップを復元する
バックアップのタイムスケジュールは以下の通り
リストアテスト用のバックアップタイムスケジュール
リストア手順は概ねこれに基づく
SSMSからSQL Serverのデータベースをリストアする
設定項目のテンプレ: データベースの復元#64e6b5bcf4582000005d682d
確認方法は概ねこれに基づく
リストア履歴をシステムデータベースから確認するSQL
確認したいシチュエーション
リストアテスト用のバックアップタイムスケジュール#64e6be81f45820000036fa03の☆、21日14時半の時点までリストアしたい
パターン1: 最新のバックアップファイルから戻せる場合
完全2.0、差分2.1、ログ2.1.0～ログ2.1.14を使う
ref: 検証: リストアの正常パターン#64e41b2ff458200000307410
パターン2: 最新の完全バックアップ(完全2.0)が壊れていた場合
完全1.0、差分1.4、ログ1.4.0～ログ2.1.14を使う
ref: 検証: リストアの正常パターン#64e41b50f458200000307417
パターン3: 最新の完全バックアップ(完全2.0)と差分バックアップが壊れていた場合
完全1.0、ログ1.12.0～ログ2.1.14を使う
ref: 検証: リストアの正常パターン#64e41b1ff45820000030740f
パターン4: 一度完全バックアップで復旧し、recovery状態で戻していく場合
完全2.0を適用、その後差分2.1を適用、その後ログ2.1.0～ログ2.1.14
ref: 検証: リストアの正常パターン#64e6cb13f458200000fb428f
最新の情報までリストアしたい
パターン5: ログ末尾のバックアップをしてリストア
使用: 最新の完全、最新の差分、残りすべてのログ
ref: 検証: リストアの正常パターン#64e6c0bff458200000390e2e
パターン1
状況
21日の14時半の時点までリストアしたい
必要なバックアップ
完全2.0
差分2.1
ログ2.1.0～ログ2.1.14
準備
新データベースtest_1を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェック
復元の前にログ末尾のバックアップを実行するのチェックを外す
実行結果
データベースtest_1の復元に成功しました。
最新データは「21(月) 14:30:01.590」でほぼ想定通り
なんで14:30:01.590のレコードまで復元されてるんだろう？
ログを確認したらトランザクションログバックアップの実行時間が3秒だった
バックアップ中に発生したトランザクションはそこまで含めてバックアップとってくれるって絵で見てわかるSQL Serverの仕組みに書いてあった気がする…ページは忘れた
さらに、バックアップが実行されている間に行われたデータベースへの更新処理の変更分もバックアップに含めるため、バックアップ開始時からバックアップ終了までの間のトランザクションログのバックアップも一緒に取得されます。
/gosyujin-books/0267: 絵で見てわかるSQL Serverの仕組み.pdf#64cc4ab22c53d80000bbdd4a
データ
table:テーブル
id created_at
... ...
... 2023-08-21 14:29:01.000
... 2023-08-21 14:30:01.590
table:restore情報
date dest filegroup restore_type device_name physical replace recovery restart stop_at stop_before
... test_1 PRIMARY データベース 08_20_0000~.bak test_1.mdf 指定 norecovery なし NULL NULL
... test_1 PRIMARY データベース 08_20_0000~.bak test_1_1.ldf 指定 norecovery なし NULL NULL
... test_1 PRIMARY 差分 08_21_0000~.bak test_1.mdf なし norecovery なし NULL NULL
... test_1 PRIMARY 差分 08_21_0000~.bak test_1_1.ldf なし norecovery なし NULL NULL
... test_1 NULL ログ 08_21_0030~.trn test_1_1.ldf なし norecovery なし NULL NULL
... test_1 NULL ログ 08_21_0130~.trn test_1_1.ldf なし norecovery なし NULL NULL
... ...
... test_1 NULL ログ 08_21_1430~.trn test_1_1.ldf なし recovery なし NULL NULL
パターン2
状況
21日の14時半の時点までリストアしたい
ただし、完全2.0は壊れている
必要なバックアップ
完全1.0
差分1.4
ログ1.4.0～ログ2.1.14
準備
新データベースtest_2を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェック
復元の前にログ末尾のバックアップを実行するのチェックを外す
実行結果
データベースtest_2の復元に成功しました。
最新データは「21(月) 14:30:01.590」で想定通り
データ
table:テーブル
id created_at
... ...
... 2023-08-21 14:29:01.000
... 2023-08-21 14:30:01.590
table:restore情報
date dest filegroup restore_type device_name physical replace recovery restart stop_at stop_before
... test_2 PRIMARY データベース 08_15_1152~.bak test_2.mdf 指定 norecovery なし NULL NULL
... test_2 PRIMARY データベース 08_15_1152~.bak test_2_1.ldf 指定 norecovery なし NULL NULL
... test_2 PRIMARY 差分 08_19_0000~.bak test_2.mdf なし norecovery なし NULL NULL
... test_2 PRIMARY 差分 08_19_0000~.bak test_2_1.ldf なし norecovery なし NULL NULL
... test_2 NULL ログ 08_19_0030~.trn test_2_1.ldf なし norecovery なし NULL NULL
... test_2 NULL ログ 08_19_0130~.trn test_2_1.ldf なし norecovery なし NULL NULL
...
... test_2 NULL ログ 08_20_0030~.trn test_2_1.ldf なし norecovery なし NULL NULL
...
... test_2 NULL ログ 08_21_1430~.trn test_2_1.ldf なし recovery なし NULL NULL
パターン3
状況
21日の14時半の時点までリストアしたい
ただし、完全2.0および差分1.1～差分1.4は壊れている
必要なバックアップ
完全1.0
ログ1.12.0～ログ2.1.14
準備
新データベースtest_3を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェック
復元の前にログ末尾のバックアップを実行するのチェックを外す
実行結果
データベースtest_3の復元に成功しました。
最新データは「21(月) 14:30:01.590」で想定通り
データ
table:テーブル
id created_at
... ...
... 2023-08-21 14:29:01.000
... 2023-08-21 14:30:01.590
table:restore情報
date dest filegroup restore_type device_name physical replace recovery restart stop_at stop_before
... test_3 PRIMARY データベース 08_15_1152~.bak test_3.mdf 指定 norecovery なし NULL NULL
... test_3 PRIMARY データベース 08_15_1152~.bak test_3_1.ldf 指定 norecovery なし NULL NULL
... test_3 NULL ログ 08_15_1230~.trn test_3_1.ldf なし norecovery なし NULL NULL
... test_3 NULL ログ 08_15_1330~.trn test_3_1.ldf なし norecovery なし NULL NULL
...
... test_3 NULL ログ 08_20_0030~.trn test_3_1.ldf なし norecovery なし NULL NULL
...
... test_3 NULL ログ 08_21_1430~.trn test_3_1.ldf なし recovery なし NULL NULL
パターン4
状況
21日の14時半の時点までリストアしたい
ただし、完全2.0をリストアし、その後差分2.1をリストア、ログをリストアと段階を踏む
そのときのデータベースの状態を確認する
必要なバックアップ
完全1.0
差分2.1
ログ2.1.0～ログ2.1.14
準備
新データベースtest_6を作成し、データベースの復元
設定
完全1.0のバックアップファイルのみ選択
既存のデータベースを上書きするをチェック
復旧状態: RESTORE WITH NORECOVERYを選択
復元の前にログ末尾のバックアップを実行するのチェックを外す
実行結果
データベースtest_6の復元に成功しました。
ただし、RESTORE WITH NORECOVERYを選択したのでtest_6のテーブルは開けない
データベース 'sample' を開けません。復元中です。
test_6が復元しています…
データ
復旧情報が見たいので、restore情報に出力するデータにsys.databasesのstate_descを足した
table:restore情報
date from from_state dest dest_state restore_type replace recovery restart stop_at stop_before
... prod_db ONLINE test_6 RESTORING データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_6 RESTORING データベース 指定 norecovery なし NULL NULL
差分復元のためさらに続く
準備
データベースの復元
設定
差分2.1のバックアップファイルのみ選択
既存のデータベースを上書きするをチェック
復旧状態: RESTORE WITH NORECOVERYを選択
復元の前にログ末尾のバックアップを実行するのチェックを外す
実行結果
データベースtest_6の復元に成功しました。
データ
table:restore情報
date from from_state dest dest_state restore_type replace recovery restart stop_at stop_before
... prod_db ONLINE test_6 RESTORING データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_6 RESTORING データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_6 RESTORING 差分 指定なし norecovery なし NULL NULL
... prod_db ONLINE test_6 RESTORING 差分 指定なし norecovery なし NULL NULL
ログ復元のためさらに続く
準備
データベースの復元
設定
ログのバックアップファイルのみ選択
既存のデータベースを上書きするをチェック
復旧状態: RESTORE WITH RECOVERYのまま
ここで終わらせる
復元の前にログ末尾のバックアップを実行するのチェックを外す
実行結果
データベースtest_6の復元に成功しました。
test_6の復元しています…が消えた
データ
table:restore情報
date from from_state dest dest_state restore_type replace recovery restart stop_at stop_before
... prod_db ONLINE test_6 ONLINE データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_6 ONLINE データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_6 ONLINE 差分 指定なし norecovery なし NULL NULL
... prod_db ONLINE test_6 ONLINE 差分 指定なし norecovery なし NULL NULL
... prod_db ONLINE test_6 ONLINE ログ 指定なし norecovery なし NULL NULL
...
... prod_db ONLINE test_6 ONLINE ログ 指定なし recovery なし NULL NULL
パターン5
状況
最新の時点までリストアしたい
必要なバックアップ
最新の完全
最新の差分
それ以降のログすべて
準備
新データベースtest_7を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェック
復元の前にログ末尾のバックアップを実行するはチェック
ソース データベースを復元中の状態にしておくもチェック
実行結果
データベースtest_7の復元に成功しました。
「prod_db」が復元しています…になる
prod_dbがブロックされるのでこれを意図せずやってしまうと困ることになる
test_7はrecovery状態になっているのでアクセスできる
データ
table:backup情報
date from physical_device_name user_name type
... 00:00:00 prod_db ...000000_1234567.bak NT SERVICE\SQLSERVERAGENT 差分バックアップ
... 00:30:00 prod_db ...003000_1234567.trn NT SERVICE\SQLSERVERAGENT ログバックアップ
...
...15:30:00 prod_db ...153000_1234567.trn NT SERVICE\SQLSERVERAGENT ログバックアップ
...16:24:59 prod_db ...LogBackup_...16-24-59.bak DOMAIN\Administrator ログバックアップ
table:restore情報
date from from_state dest dest_state restore_type replace recovery restart stop_at stop_before
... prod_db RESTORING test_7 ONLINE データベース 指定 norecovery なし NULL NULL
... prod_db RESTORING test_7 ONLINE データベース 指定 norecovery なし NULL NULL
... prod_db RESTORING test_7 ONLINE 差分 指定なし norecovery なし NULL NULL
... prod_db RESTORING test_7 ONLINE 差分 指定なし norecovery なし NULL NULL
... prod_db RESTORING test_7 ONLINE ログ 指定なし norecovery なし NULL NULL
...
... prod_db RESTORING test_7 ONLINE ログ 指定なし recovery なし NULL NULL
復元しています…を解消するSQL
table:restore情報
date from from_state dest dest_state restore_type replace recovery restart stop_at stop_before
... prod_db ONLINE test_7 ONLINE データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_7 ONLINE データベース 指定 norecovery なし NULL NULL
... prod_db ONLINE test_7 ONLINE 差分 指定なし norecovery なし NULL NULL
... prod_db ONLINE test_7 ONLINE 差分 指定なし norecovery なし NULL NULL
... prod_db ONLINE test_7 ONLINE ログ 指定なし norecovery なし NULL NULL
...
... prod_db ONLINE test_7 ONLINE ログ 指定なし recovery なし NULL NULL